import random
import time
from database import Mysql
import conf
from utils import adjust_time

# 工单时间参数 (小时)
# 每道工序的时间
step_hour = 6
# 每道工序的计划时间
step_plan_hour = 6.2
random_0_300s = random.randint(0, 300) * 1000  # 0-300秒


def update_prod(db):
    # 获取当前毫秒级时间戳（替代UNIX_TIMESTAMP() * 1000）
    current_timestamp = int(time.time() * 1000)

    # 1、更新mes_task_master表，
    # 设置plan_start_time、plan_end_time、actual_start_time、actual_end_time、bill_time、create_time（基于sale_order的audit_time）
    sql1 = """
        SELECT mt.id, so.order_code, so.order_time, so.audit_time 
        FROM mes_task_master mt
        JOIN sale_order so ON mt.sale_order_no = so.order_code
    """
    results = db.get_all(sql1)
    update_sql1 = """
        UPDATE mes_task_master 
        SET plan_start_time = %s, 
            plan_end_time = %s, 
            actual_start_time = %s, 
            actual_end_time = %s, 
            bill_time = %s, 
            create_time = %s 
        WHERE id = %s
    """
    if not results:
        return
    for result in results:
        id = int(result["id"])
        order_time = result["order_time"]
        audit_time = result["audit_time"]

        t = audit_time + random.randint(10 * 60 * 1000, 20 * 60 * 1000)  # 将计划开始时间设置为审核时间往后加10-20分钟
        t = adjust_time(t)
        plan_start_time = t
        plan_end_time = t + 30 * 24 * 60 * 60 * 1000  # 将计划结束时间设置为计划开始时间往后加一个月
        actual_start_time = t + random.randint(3 * 60 * 1000, 10 * 60 * 1000)  # 将实际开始时间设置为计划开始时间往后加3-10分钟
        actual_end_time = actual_start_time + random.randint(72 * 60 * 60 * 1000,
                                                             88 * 60 * 60 * 1000)  # 将实际结束时间设置为实际开始时间往后加72至88小时
        actual_end_time = adjust_time(actual_end_time)
        bill_time = order_time  # 将单据日期设置为订单日期
        create_time = t  # 将创建时间设置为t值

        count1 = db.update(update_sql1, (
            plan_start_time, plan_end_time, actual_start_time, actual_end_time, bill_time, create_time, id))
        if count1 > 0:
            print("UPDATE 执行成功，影响了 {} 行".format(count1))
        else:
            print("UPDATE 执行失败{} {}".format(update_sql1, (
                plan_start_time, plan_end_time, actual_start_time, actual_end_time, bill_time, create_time, id)))
            return

    # 2、同步生产子任务时间信息为生产主任务时间（一个主任务对应一个子任务）
    sql2 = """
        UPDATE mes_task_line mtl
        JOIN mes_task_master mtm ON mtl.task_id = mtm.id
        SET
            mtl.plan_start_time = mtm.plan_start_time,
            mtl.plan_end_time = mtm.plan_end_time,
            mtl.actual_start_time = mtm.actual_start_time,
            mtl.actual_end_time = mtm.actual_end_time,
            mtl.last_update_time = %s  # 使用Python生成的当前时间戳
        WHERE
            mtl.task_id IS NOT NULL
    """
    count2 = db.update(sql2, (current_timestamp,))
    print(f"SQL2 同步生产子任务时间: 影响 {count2} 行")

    # 3、基于生产主任务更新生产工单的计划开始时间、计划结束时间、实际开始时间、实际结束时间，
    # 实际工作时间为计划工作时间减去0-0.8个小时的随机值
    sql3 = """
        UPDATE mes_task_work_order st
        JOIN mes_task_line t ON st.sub_task_id = t.id
        SET 
            st.plan_start_time = t.plan_start_time + (CAST(st.item_no AS SIGNED) - 1) * %s * 3600 * 1000,
            st.plan_end_time = t.plan_start_time + CAST(st.item_no AS SIGNED) * %s * 3600 * 1000,
            st.actual_working_hour = st.plan_working_hour - (ROUND(FLOOR(RAND() * 9) / 10, 1)),
            st.actual_start_time = t.actual_start_time + (CAST(st.item_no AS SIGNED) - 1) * %s * 3600 * 1000 + %s,
            st.actual_end_time = t.actual_start_time + CAST(st.item_no AS SIGNED) * %s * 3600 * 1000 + %s
    """
    count3 = db.update(
        sql3,
        (
            step_plan_hour,
            step_plan_hour,
            step_hour,
            random_0_300s,
            step_hour,
            random_0_300s,
        ),
    )
    print(f"SQL3 同步生产工单时间: 影响 {count3} 行")

    # 4、生产任务异常信息处理
    # 查询需要处理的异常记录及关联的任务时间信息
    sql4 = """
        SELECT mtwore.id, mtl.actual_start_time, mtl.actual_end_time
        FROM mes_task_work_order_ex mtwore
        LEFT JOIN mes_task_line mtl ON mtwore.sub_task_id = mtl.id
        WHERE mtl.id IS NOT NULL
    """
    exception_records = db.get_all(sql4)
    count4 = 0

    if exception_records:
        update_sql4 = """
            UPDATE mes_task_work_order_ex
            SET create_time = %s, last_update_time = %s
            WHERE id = %s
        """

        for record in exception_records:
            record_id = record['id']
            actual_start = record['actual_start_time']
            actual_end = record['actual_end_time']

            # 仅当开始和结束时间都存在时计算随机时间
            if actual_start and actual_end and actual_end > actual_start:
                # 计算时间差并生成随机时间点（Python实现原SQL中的FLOOR(RAND() * ...)逻辑）
                time_diff = actual_end - actual_start
                random_offset = random.randint(0, time_diff)
                new_time = actual_start + random_offset
                new_time = adjust_time(new_time)
                # 执行更新
                temp_count = db.update(update_sql4, (new_time, new_time, record_id))
                count4 += temp_count

    print(f"SQL4 生产异常信息处理: 影响 {count4} 行")

    # 5、修改生产任务检验合规数量
    sql5 = """
        UPDATE mes_check_task_item mcti
        RIGHT JOIN mes_check_task mct ON mct.id = mcti.check_task_id
        SET mcti.ok_num = mct.check_num
        WHERE mct.wip_id != ''
    """
    count5 = db.update(sql5)
    print(f"SQL5 修改检验合规数量: 影响 {count5} 行")

    db.commit()


if __name__ == "__main__":
    python_timestamp = int(time.time())
    print(f"Python生成的毫秒级时间戳: {python_timestamp}")




